package com.wxtf.platform.main.jcqkgl;

import java.io.File;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: only_childParent_retireAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.only_childParent_retireAction</li>
 * <li>Description: 退休独生子女父母奖励费</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class only_childParent_retireAction extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(only_childParent_retireAction.class);

	public only_childParent_retireAction(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if("updatedata".equals(this.getMethodName())) {
				msg = this.updateData(this.getLogicData());
			} else if("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			}else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {"trpocrr_id","org_id","tbe_id","trpocrr_name","trpocrr_sex","trpocrr_age","trpocrr_birthday",
						"trpocrr_ocr_time","trpocrr_position","trpocrr_get_money","trpocrr_time","trpocrr_person",
						"trpocrr_memo","reserve1", "reserve2", "reserve3", "reserve4", "reserve5", "create_by", "create_time",
						"last_modify_by", "last_modify_time"};
	
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String money=user.getGlobalMap().get("Retirement");

		sql = " from (select trpocrr_id,e.org_id,e.tbe_id,e.tbe_name as trpocrr_name,e.tbe_sex as trpocrr_sex,e.tbe_age as trpocrr_age,e.tbe_birthday as trpocrr_birthday,o.tbeocr_time as trpocrr_ocr_time,e.tbe_position as trpocrr_position,'"+money+"' as trpocrr_get_money,trpocrr_time,trpocrr_person," +
				"trpocrr_memo,e.reserve1,r.reserve2,r.reserve3,r.reserve4,r.reserve5,r.create_by,r.create_time,r.last_modify_by,r.last_modify_time from tbe_only_child_record o LEFT JOIN tb_family_base f on o.tbfi_id=f.tbfi_id " +
				"LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id LEFT JOIN trp_only_child_retire_reward r on e.tbe_id=r.tbe_id where o.reserve2<>'1' and e.tbe_employment_forms <> '31' and e.tbe_status not in('102-4','102-3'))t where 1=1";
		//要求获取独生子女证的夫妻,达到退休年龄的职工【男职工60，女职工50，女干部55】
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				sql += " and org_id in (" + user.getCurSubOrgId() + ")";
				//sql += " and trpocrr_id is not null";//将已发放的默认显示
				//以当前时间为起点
				sql +=" and tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 60 YEAR)";
				sql +=" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 50 YEAR)";
				sql +=" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 55 YEAR))";
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String trpocrr_position = jsonObject.getString("trpocrr_position");//职务
				String reserve2 = jsonObject.getString("reserve2");//是否发放
				String time1 = jsonObject.getString("time1");// 开始时间
				String time2 = jsonObject.getString("time2");// 结束时间
				
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}

				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and trpocrr_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(trpocrr_position)){
					if(!trpocrr_position.equals("0") && reserve2 != "")
					{
						buff.append(" and trpocrr_position = '").append(trpocrr_position).append("'");
					}
				}
				if(StringUtils.isNotBlank(reserve2)){
					if(!reserve2.equals("2") && reserve2 != "")
					{
						buff.append(" and reserve2 = '").append(reserve2).append("'");//是否已发放
					}
				}
				if(StringUtils.isBlank(time1)&&StringUtils.isBlank(time2))
				{
					buff.append(" and tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 60 YEAR)");
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 50 YEAR)");
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 55 YEAR))");
				}
				if(StringUtils.isNotBlank(time1)&&StringUtils.isNotBlank(time2))
				{
					buff.append(" and tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
				}
				else
				{
					if(StringUtils.isNotBlank(time1)) {
						buff.append(" and tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)");//男职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)");//女职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR))");//女干部
					}
					if(StringUtils.isNotBlank(time2)) {
						buff.append(" and tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
					}
				}
				sql += buff.toString();
			} 
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = " select * " + sql;
//		System.out.println(querySql);
		String strJson = AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
		int Totaled = 0;
		String t1="select count(*) "+sql+" and trpocrr_id <>''";
		Totaled=AccessDataBaseUtil.queryForInt(t1, null);
		return strJson.substring(0, strJson.indexOf(","))+",\"Totaled\":\"" + Totaled + "\""+ strJson.substring(strJson.indexOf(","), strJson.length());
	}
	
	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		buff.append("insert into trp_only_child_retire_reward(trpocrr_id");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
		}
		buff.append(") values(");
		obj[0] = AccessDataBaseUtil.getPKUUID();
	
		String curTime = FormatUtil.getCurentDateTime();
		obj[18] = user.getUserId();
		obj[19] = curTime;
		obj[20] = user.getUserId();
		obj[21] = curTime;
		
		for(int i=0; i<obj.length; i++){
			buff2.append("'").append(obj[i]).append("',");
		}
		buff2.deleteCharAt(buff2.length() - 1);
		String sql1 = buff.append(buff2.toString()).append(")").toString();
		AccessDataBaseUtil.executeSQLUpdate(sql1);
		return "\"发放成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		buff.append("update trp_only_child_retire_reward set ");
		Object[] obj = new Object[fields.length];
		for(int j = 1; j < fields.length; j++) {
			obj[j - 1] = jsonObject.getString(fields[j]);
			buff.append(fields[j]).append(" = ?,");
		}
		buff.deleteCharAt(buff.length() - 1).append(" where trpocrr_id = ?");
		obj[19] = user.getUserId();
		obj[20] = curTime;
		obj[21] = jsonObject.getString("trpocrr_id");
		
		AccessDataBaseUtil.executeSQLUpdate(buff.toString(), obj);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		sql = "delete from trp_only_child_retire_reward where trpocrr_id = ?";
		
		AccessDataBaseUtil.deleteByIds(sql, ids);
		return "\"取消发放成功\"";
	}
	
	///发放奖励费
	public Object updateData(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		id = "'"+id+"'";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		String curuser = user.getUserId();
		String sqlinto="Insert into trp_only_child_retire_reward(trpocrr_id,tbe_id,org_id,trpocrr_name,trpocrr_sex,trpocrr_age,trpocrr_birthday,trpocrr_position,trpocrr_get_money,reserve1,reserve2,create_by, create_time,last_modify_by, last_modify_time)" +
		"select REPLACE(uuid(),'-',''),tbe_id,org_id,tbe_name,tbe_sex,tbe_age,tbe_birthday,tbe_position,'"+user.getGlobalMap().get("Retirement")+"',reserve1,'1','"+curuser+"','"+curTime+"','"+curuser+"','"+curTime+"' from tb_employee where tbe_id in ("+id+")";

		AccessDataBaseUtil.executeSQLUpdate(sqlinto);
		return "\"发放成功\"";
	}	
	

	/** 
	 * 描述：expData 人员导出
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn 
	 * @author wangli
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String orgIds = jsonObject.getString("orgIds");//机构ID串
		String tbe_name = jsonObject.getString("tbe_name");//姓名
		String trpocrr_position = jsonObject.getString("trpocrr_position");//职务
		String reserve2 = jsonObject.getString("reserve2");//是否发放
		String time1 = jsonObject.getString("time1");// 开始时间
		String time2 = jsonObject.getString("time2");// 结束时间

		StringBuffer buff = new StringBuffer();
		buff.append("SELECT e.reserve1,e.tbe_name,e.tbe_sex,e.tbe_birthday,o.tbeocr_time as trpocrr_ocr_time,case tbe_position when '1' then '专业技术管理' when '2' then '生产操作服务' end,");
		buff.append("trpocrr_get_money,trpocrr_time,trpocrr_person,trpocrr_memo");
		buff.append(" from tbe_only_child_record o LEFT JOIN tb_family_base f on o.tbfi_id=f.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id LEFT JOIN trp_only_child_retire_reward r on e.tbe_id=r.tbe_id where o.reserve2<>'1' and e.tbe_employment_forms <> '31' and e.tbe_status not in('102-4','102-3')");

		if(StringUtils.isBlank(time1)&&StringUtils.isBlank(time2))
		{
			buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 60 YEAR)");
			buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 50 YEAR)");
			buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 55 YEAR))");
		}
		
		if(StringUtils.isBlank(orgIds)){
			buff.append(" and e.org_id in (").append(user.getCurSubOrgId()).append(")");
		} else {
			buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
		}
		if(StringUtils.isNotBlank(tbe_name)){
			buff.append(" and e.tbe_name like '%").append(tbe_name).append("%'");
		}
		if(StringUtils.isNotBlank(trpocrr_position)){
			if(!trpocrr_position.equals("0") && reserve2 != "")
			{
				buff.append(" and r.trpocrr_position = '").append(trpocrr_position).append("'");
			}
		}
		if(StringUtils.isNotBlank(reserve2)){
			if(!reserve2.equals("2") && reserve2 != "")
			{
				buff.append(" and r.reserve2 = '").append(reserve2).append("'");//是否已发放
			}
		}
		if(StringUtils.isNotBlank(time1)&&StringUtils.isNotBlank(time2))
		{
			buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
			buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
			buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
		}
		else
		{
			if(StringUtils.isNotBlank(time1)) {
				buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)");//男职工
				buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)");//女职工
				buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR))");//女干部
			}
			if(StringUtils.isNotBlank(time2)) {
				buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
				buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
				buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
			}
		}
		//System.out.println("querySql:" + buff.toString());
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/retire_reward.xls";
		String file = url + "/file/retire_reward.xls";
		String rfile = "jsp/app/xls/file/retire_reward.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
		if(list.size() > 0) {
			for(int i = 0; i < list.size(); i++) {
				Object[] obj=list.get(i);
				//男职工2000元，女职工2000元，女干部2000元
				obj[6] = "2000";
				//obj[7]=user.getGlobalMap().get("Retirement");//从参数中取得
			}
		}
		if(list.size() > 0){
			XLSUtils.writeXls(list, fto, "退休独生子女父母奖励信息一览表", 1);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		return "\"" + rfile + "\"";
	}
	
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}


